﻿use MusicmanStaging
go 

if exists (select * from musicmanstaging.information_schema.routines where 
routine_name = 'DeleteUserPost' and 
routine_schema = 'dbo' and 
routine_type = 'procedure')
begin 
	DROP PROCEDURE [dbo].[DeleteUserPost]
end 
go

CREATE PROCEDURE [dbo].[DeleteUserPost]
	@ID bigint
AS
BEGIN
	DELETE FROM dbo.UserPost WHERE ParentID = @ID
	DELETE FROM dbo.UserPost WHERE ID = @ID
END
GO

if exists (select * from musicmanstaging.information_schema.routines where 
routine_name = 'UpdateFileStats' and 
routine_schema = 'dbo' and 
routine_type = 'procedure')
begin 
	DROP PROCEDURE [dbo].[UpdateFileStats]
end 
go

CREATE PROCEDURE  [dbo].[UpdateFileStats]
	@SearchFileID bigint,
	@mark int
AS
BEGIN

 DECLARE @TotalVoted int;
 DECLARE @Rating decimal(18,2);
 
 IF EXISTS(SELECT * FROM dbo.FilesStats WHERE SearchFileID = @SearchFileID)
 BEGIN
	DECLARE @Total decimal(18,2);
	SELECT @TotalVoted = TotalVoted, @Rating = Rating FROM dbo.FilesStats WHERE SearchFileID = @SearchFileID
	SET @Total =  (@TotalVoted * @Rating + @mark) / (@TotalVoted + 1);
	UPDATE dbo.FilesStats SET Rating = @Total, TotalVoted = @TotalVoted + 1 WHERE SearchFileID = @SearchFileID
 END
 ELSE 
 BEGIN 
   INSERT INTO dbo.FilesStats (SearchFileID, TotalDownloads, TotalVoted, Rating) VALUES (@SearchFileID, 0, 1, @mark)
 END 
END

if exists (select * from musicmanstaging.information_schema.routines where 
routine_name = 'DeleteUser' and 
routine_schema = 'dbo' and 
routine_type = 'procedure')
begin 
	DROP PROCEDURE [dbo].[DeleteUser]
end 
go

CREATE PROCEDURE [dbo].[DeleteUser]
	@PersonID bigint
AS
BEGIN
	DELETE FROM	dbo.UserHistory WHERE PersonID = @PersonID
	DELETE FROM dbo.userdownloads WHERE UserID = @PersonID
	DELETE FROM dbo.userroles WHERE UserID = @PersonID
	
	-- DELETE ALL COMMENTS OF MESSAGES TO BE DELETED
	DELETE FROM dbo.UserPost WHERE ParentID in (SELECT ID FROM dbo.UserPost WHERE Personid = @PersonID)
	-- DELETE ALL COMMENTS OF PERSON TO BE DELETED
	DELETE FROM dbo.UserPost WHERE ParentID IS NOT NULL AND Personid = @PersonID
	-- DELETE ALL MESSAGES OF PERSON TO BE DELETED
	DELETE FROM dbo.UserPost WHERE Personid = @PersonID

	DELETE FROM dbo.userstats WHERE Personid = @PersonID
	DELETE FROM dbo.users WHERE Personid = @PersonID
END

if exists (select * from musicmanstaging.information_schema.routines where 
routine_name = 'UpdateDownloadStats' and 
routine_schema = 'dbo' and 
routine_type = 'procedure')
begin 
	DROP PROCEDURE [dbo].[UpdateDownloadStats]
end 
go

CREATE PROCEDURE [dbo].[UpdateDownloadStats]
	@PersonId bigint,
	@SearchFileID bigint,
	@fileLenght bigint,
	@filename nvarchar(300),
	@num int
AS
BEGIN
 IF (@SearchFileID is not null)
 BEGIN  
	 IF NOT EXISTS(SELECT * FROM DBO.FILESSTATS WHERE SEARCHFILEID = @SearchFileID)
	 BEGIN
		INSERT INTO DBO.FILESSTATS(SEARCHFILEID, TOTALDOWNLOADS) VALUES (@SearchFileID, @num);
	 END 
	 ELSE 
	 BEGIN 
		UPDATE DBO.FILESSTATS SET TOTALDOWNLOADS = TOTALDOWNLOADS + @num WHERE SEARCHFILEID = @SearchFileID
	 END
 END
 
 UPDATE DBO.USERSTATS SET TOTALFILES = TOTALFILES + 1, TOTALLENGTH = TOTALLENGTH + @fileLenght WHERE PERSONID = @PersonId
 insert into dbo.UserHistory(personid, SearchFileID, name) values (@PersonId, @SearchFileID, @filename);
END
go

-- procedure clear job data
if exists (select * from musicmanstaging.information_schema.routines where 
routine_name = 'ClearJobData' and 
routine_schema = 'dbo' and 
routine_type = 'procedure')
begin 
	DROP PROCEDURE [dbo].[ClearJobData]
end 
go

CREATE PROCEDURE [dbo].[ClearJobData]
	@SearchJobID bigint
AS
BEGIN

    -- delete all pending downloads
	delete from dbo.userDownloads where SearchFielID in 
	(select f1.SearchFileID from dbo.SearchJob j1
		join dbo.SearchFile f1 on j1.SearchJobID = f1.SearchJobID
		join dbo.userDownloads u1 on u1.SearchFielID = f1.SearchFileID
	 where j1.SearchJobID = @SearchJobID)

	-- update user history table
	update dbo.UserHistory set SearchFileID = null where SearchFileID in 
	(select f1.SearchFileID from dbo.SearchJob j1
		join dbo.SearchFile f1 on j1.SearchJobID = f1.SearchJobID
		join dbo.userHistory uh1 on uh1.SearchFileID = f1.SearchFileID
	 where j1.SearchJobID = @SearchJobID)

    -- delete all files stats
	delete from dbo.filesstats where SearchFileID in 
	(select f1.SearchFileID from dbo.SearchJob j1
		join dbo.SearchFile f1 on j1.SearchJobID = f1.SearchJobID
		join dbo.filesstats fs on fs.SearchFileID = f1.SearchFileID
	 where j1.SearchJobID = @SearchJobID)

	-- delete all files
	delete from dbo.SearchFile where SearchFileID in 
	(select f1.SearchFileID from dbo.SearchJob j1
		join dbo.SearchFile f1 on j1.SearchJobID = f1.SearchJobID
	 where j1.SearchJobID = @SearchJobID)
END
